package com.nj.dms.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.nj.dms.bean.FileBean;
import com.nj.dms.dao.FileDao;
import com.nj.dms.util.DbUtil;

/**
 * 文件数据访问对象实现类
 * 
 * @author Administrator
 *
 */
public class FileDaoImpl implements FileDao {

	@Override
	public boolean addFile(FileBean file) {

		// 连接数据库
		Connection conn = DbUtil.dbOpen();

		PreparedStatement pstmt = null;

		int i = 0;
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

		try {
			pstmt = conn.prepareStatement(
					"insert into file(user_name,name,description,path,type,thick,audit,version,publish_date,update_date) values(?,?,?,?,?,?,?,?,?,?)");

			pstmt.setString(1, file.getUserName());
			pstmt.setString(2, file.getName());
			pstmt.setString(3, file.getDescription());
			pstmt.setString(4, file.getPath());
			pstmt.setInt(5, file.getType());
			pstmt.setInt(6, file.getThick());
			pstmt.setInt(7, file.getAudit());
			pstmt.setString(8, file.getVersion());
			pstmt.setString(9, sdf.format(file.getPublishDate()));
			pstmt.setString(10, sdf.format(file.getUpdateDate()));

			i = pstmt.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtil.dbClose(conn, pstmt);
		}

		return i > 0;
	}

	@Override
	public boolean saveFile(FileBean file) {

		// 保存文章

		// 获取数据库连接

		Connection conn = DbUtil.dbOpen();

		PreparedStatement pstmt = null;
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

		int i = 0;

		try {
			pstmt = conn.prepareStatement(
					"update file set description=?, thick=?, audit=?, version=?, update_date=? where id=?");

			pstmt.setString(1, file.getDescription());
			pstmt.setInt(2, file.getThick());
			pstmt.setInt(3, file.getAudit());
			pstmt.setString(4, file.getVersion());
			pstmt.setString(5, sdf.format(file.getUpdateDate()));
			pstmt.setInt(6, file.getId());

			i = pstmt.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {

			DbUtil.dbClose(conn, pstmt);

		}

		return i > 0;
	}

	@Override
	public boolean deleteFile(int id) {
		// 删除文章
		Connection conn = DbUtil.dbOpen();
		PreparedStatement pstmt = null;
		int i = 0;

		try {
			pstmt = conn.prepareStatement("delete from file where id=?");

			pstmt.setInt(1, id);

			i = pstmt.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtil.dbClose(conn, pstmt);
		}

		return i > 0;
	}

	@Override
	public boolean deleteFile(String[] ids) {

		Connection con = DbUtil.dbOpen();
		// 定义一个int类型的数组，保存数据增加、删除、修改的每一批次返回的影响记录数
		int[] i = null;
		PreparedStatement pst = null;

		// String sql="delete from user where userid in( 1,2,3 ) ";

		String sql = "delete from file where id=?";
		try {
			// 调用Connection类的setAutoCommit(false)方法,将自动提交该为手动提交方式
			con.setAutoCommit(false);

			pst = con.prepareStatement(sql);
			for (int j = 0; j < ids.length; j++) {
				// 调用Integer包装类的parseInt(String),将数字字符串转换为整数
				pst.setInt(1, Integer.parseInt(ids[j]));
				// 调用PreparedStatement类的addBatch()方法，保存一次数据处理的有参数的sql语句
				pst.addBatch();
			}
			// 调用PreparedStatement类的executeBatch()方法，写入数据
			i = pst.executeBatch();
			// 正式向数据库写入数据
			con.commit();

		} catch (Exception e) {
			e.getMessage();
		} finally {
			DbUtil.dbClose(con, pst);
		}
		return i.length > 0;
	}

	@Override
	public FileBean findFile(int id) {

		Connection conn = DbUtil.dbOpen();

		ResultSet rs = null;

		PreparedStatement pstmt = null;

		FileBean file = new FileBean();

		try {

			pstmt = conn.prepareStatement(
					"select id, user_name, name, description, path, type, thick, audit, version, publish_date, update_date from file where id=?");

			pstmt.setInt(1, id);

			rs = pstmt.executeQuery();

			if (rs != null && rs.next()) {

				file.setId(rs.getInt("id"));
				file.setUserName(rs.getString("user_name"));
				file.setName(rs.getString("name"));
				file.setDescription(rs.getString("description"));
				file.setPath(rs.getString("path"));
				file.setType(rs.getInt("type"));
				file.setThick(rs.getInt("thick"));
				file.setAudit(rs.getInt("audit"));
				file.setVersion(rs.getString("version"));
				file.setPublishDate(new Date(rs.getTimestamp("publish_date").getTime()));
				file.setUpdateDate(new Date(rs.getTimestamp("update_date").getTime()));

			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {

			DbUtil.dbClose(conn, pstmt, rs);

		}

		return file;
	}

	@Override
	public List<FileBean> findAllFile() {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public String getFilePath(int id) {

		Connection conn = DbUtil.dbOpen();

		PreparedStatement pstmt = null;

		ResultSet rs = null;

		String path = null;

		try {
			pstmt = conn.prepareStatement("select path from file where id=?");

			pstmt.setInt(1, id);

			rs = pstmt.executeQuery();

			if (rs != null && rs.next()) {

				path = rs.getString("path");

			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtil.dbClose(conn, pstmt, rs);
		}

		return path;
	}

	@Override
	public List<FileBean> findAllFileByUserByPage(String userName, Integer beginIndex, int maxSize) {

		// 查找一个用户的所有文章

		List<FileBean> list = new ArrayList();

		// 连接数据库
		Connection conn = DbUtil.dbOpen();

		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			pstmt = conn.prepareStatement(
					"select id, name, path, type, thick, audit, version, publish_date from file where user_name=? limit ?,?");
			pstmt.setString(1, userName);
			pstmt.setInt(2, beginIndex);
			pstmt.setInt(3, maxSize);

			rs = pstmt.executeQuery();

			if (rs != null) {

				while (rs.next()) {

					FileBean file = new FileBean();
					file.setId(rs.getInt("id"));
					file.setName(rs.getString("name"));
					file.setPath(rs.getString("path"));
					file.setType(rs.getInt("type"));
					file.setThick(rs.getInt("thick"));
					file.setAudit(rs.getInt("audit"));
					file.setVersion(rs.getString("version"));
					file.setPublishDate(new Date(rs.getTimestamp("publish_date").getTime()));

					list.add(file);

				}

			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtil.dbClose(conn, pstmt, rs);
		}

		return list;
	}

	@Override
	public int countFilesByUser(String userName) {
		Connection conn = DbUtil.dbOpen();
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		int recordNums = 0;
		try {
			// 统计记录总数方法:select count(id) from user
			String sql = "SELECT COUNT(id) AS num FROM file WHERE user_name=?";

			pstmt = conn.prepareStatement(sql);

			pstmt.setString(1, userName);

			rs = pstmt.executeQuery();
			if (rs.next()) {
				recordNums = rs.getInt("num");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtil.dbClose(conn, pstmt, rs);
		}
		return recordNums;
	}

	@Override
	public List<String> getFilePath(String[] ids) {

		List<String> pathList = new ArrayList();

		Connection con = DbUtil.dbOpen();
		// 定义一个int类型的数组，保存数据增加、删除、修改的每一批次返回的影响记录数

		PreparedStatement pst = null;

		ResultSet rs = null;

		String sql = "select path from file where id in(";

		for (int i = 0; i < ids.length; i++) {
			sql += "?";
			if (i != ids.length - 1) {
				sql += ", ";
			}
		}
		sql += ")";

		try {

			pst = con.prepareStatement(sql);
			for (int j = 0; j < ids.length; j++) {

				pst.setInt(j + 1, Integer.parseInt(ids[j]));

			}

			rs = pst.executeQuery();

			while (rs.next()) {

				pathList.add(rs.getString("path"));

			}

		} catch (Exception e) {
			e.getMessage();
		} finally {
			DbUtil.dbClose(con, pst);
		}
		return pathList;
	}

	public static void main(String[] args) {

		FileDaoImpl fileDao = new FileDaoImpl();

		String[] ids = { "39", "41", "47", "49", "50" };

		List<String> list = fileDao.getFilePath(ids);

		for (String s : list) {

			System.out.println(s);

		}

	}

}
